#encoding: utf-8

import pymysql
from utils.getLogs import collectLogs
import traceback

class AmanMySQL():
    def __init__(self, host, user, passwd, dbName):
        self.host = host
        self.user = user
        self.passwd = passwd
        self.dbName = dbName

    def connet(self):
        try:
            # self.db = pymysql.connect(self.host, self.user, self.passwd, self.dbName)  # 不设置use_unicode=True和charset='utf8'，数据库查询时中文数据可能会有乱码
            self.db = pymysql.connect(self.host, self.user, self.passwd, self.dbName, use_unicode=True, charset='utf8')
            self.cursor = self.db.cursor()
        except Exception:
            collectLogs(loginfo='连接数据库失败，原因：%s' % traceback.format_exc(), lever='error', logger='AmanMySQL')

    def close(self):
        try:
            self.cursor.close()
            self.db.close()
        except Exception:
            collectLogs(loginfo='关闭数据库失败，原因：%s' % traceback.format_exc(), lever='error', logger='AmanMySQL')

    def get_one(self, sql):
        res = None
        try:
            self.connet()
            self.cursor.execute(sql)
            res = self.cursor.fetchone()
            self.close()
        except Exception:
            collectLogs(loginfo='数据库查询失败，原因：%s' % traceback.format_exc(), lever='error', logger='AmanMySQL')
        return res

    def get_all(self, sql):
        res = ()
        try:
            self.connet()
            self.cursor.execute(sql)
            res = self.cursor.fetchall()
            self.close()
        except Exception:
            collectLogs(loginfo='数据库查询失败，原因：%s' % traceback.format_exc(), lever='error', logger='AmanMySQL')
        return res

    def get_all_obj(self, sql, tableName, *args):
        resList = []
        fieldsList = []
        try:
            if (len(args) > 0):
                for item in args:
                    fieldsList.append(item)
            else:
                fieldsSql = "select COLUMN_NAME from information_schema.COLUMNS where table_name = '%s' and table_schema = '%s'" % (
                    tableName, self.dbName)
                fields = self.get_all(fieldsSql)
                for item in fields:
                    fieldsList.append(item[0])

            # 执行查询数据sql
            res = self.get_all(sql)
            for item in res:
                obj = {}
                count = 0
                for x in item:
                    obj[fieldsList[count]] = x
                    count += 1
                resList.append(obj)
            return resList
        except Exception:
            collectLogs(loginfo='数据库查询失败，原因：%s' % traceback.format_exc(), lever='error', logger='AmanMySQL')

    def insert(self, sql):
        return self.__edit(sql)
    def update(self, sql):
        return self.__edit(sql)
    def delete(self, sql):
        return self.__edit(sql)
    def __edit(self, sql):
        count = 0
        try:
            self.connet()
            count = self.cursor.execute(sql)
            self.db.commit()
            self.close()
        except Exception:
            collectLogs(loginfo='数据库操作失败，原因：%s' % traceback.format_exc(), lever='error', logger='AmanMySQL')
            self.db.rollback()
        return count